package uvt.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.TreeMap;

import uvt.map.graph.FromWithOnTo;
import uvt.map.graph.Node;
import uvt.map.graph.Vertex;
import uvt.map.graph.element.Segment;
import uvt.map.graph.element.Station;
import uvt.map.graph.element.Street;
import uvt.map.graph.element.StreetTreeMap;
import uvt.map.graph.element.Vehicle;
import uvt.map.graph.element.VehicleColor;

public class Database implements CreateOperations,SelectOperations,
								 InsertOperations,DeleteOperations {
	
		private static final String DATABASE = "timisora-network-map-database";
		private static final String STATION = "Station";
		private static final String VEHICLE = "Vehicle";
		private static final String STREETNAME = "StreetName";
		private static final String STREETSEGMENT = "StreetSegment";
		private static final String STREET = "Street";
		private static final String ROAD = "Road";
		private static final String ROUTENAME = "RouteName";
		private static final String ROUTE = "Route";
		
		private static Database instance;
	
		private Connection connection;
	    private Statement statement;
	    
	    private Database() {
	    }
	    
	    public static Database getInstace() {
	    	if(instance == null)
	    		instance = new Database();
	    	return instance;
	    }
	    
	    /* CREATE OPERATIONS */
	    
	    public void createTableStation() {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + STATION +
			                 " (Nr             INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  Name           CHAR(30), " +
			                 "  Latitude       CHAR(20), " + 
			                 "  Longitude      CHAR(20));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
		}
	    
	    public void createTableVehicle() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + VEHICLE +
			    		     " (Nr            INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  Name          CHAR(30)," +
			                 "  Type          CHAR(10)," +
			                 "  Route         CHAR(10));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableStreetName() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + STREETNAME +
			    		     " (Id            INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  Name          CHAR(30)," +
			    		     " FOREIGN KEY(Id) REFERENCES " + STREET + "(IdStreetName));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableStreetSegment() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + STREETSEGMENT +
			    		     " (Id            INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  XA            CHAR(20)," +
			                 "  YA            CHAR(20)," +
			                 "  XB            CHAR(20)," +
			                 "  YB            CHAR(20)," +			    		     
			    		     " FOREIGN KEY(Id) REFERENCES " + STREET + "(IdStreetSegment));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableStreet() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + STREET +	    		     
			    		     "(IdStreetSegment        INTEGER PRIMARY KEY," +
			    		     " IdStreetName           INTEGER);"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableRoad() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + ROAD +	    		     
				    		 " (Id           INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  Fr           INTEGER," +
			                 "  Onn          INTEGER," +  
			                 "  Too           INTEGER," +  
			                 "  FOREIGN KEY(Fr) REFERENCES " + STATION + "(Nr)," +
			                 "  FOREIGN KEY(Too) REFERENCES " + STATION + "(Nr)," +
			    		     "  FOREIGN KEY(Onn) REFERENCES " + STREETNAME + "(Id));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableRouteName() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + ROUTENAME +	    		     
				    		 " (Id           INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  Name         CHAR(30)," +
			                 "  Type         CHAR(10));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void createTableRoute() {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        statement = connection.createStatement();
			    String sql = "CREATE TABLE " + ROUTE +	    		     
				    		 " (Id              INTEGER PRIMARY KEY AUTOINCREMENT," +
			                 "  IdRouteName     INTEGER," +
			                 "  IdRoad          INTEGER," +		
			                 "  FOREIGN KEY(IdRouteName) REFERENCES " + ROUTENAME + "(Id)," +
			    		     "  FOREIGN KEY(IdRoad) REFERENCES " + ROAD + "(Id));"; 
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.close();			
			}  catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    /* INSERT OPERATIONS */
	    
	    public void insertRowStation(Station station) throws Exception {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "INSERT INTO " + STATION + 
			    		     " (Name,Latitude,Longitude)" +
			                 " VALUES ('" + station.getName() + "'," +
			    		     " '" + station.getLatitude() + "'," +
			    		     " '" + station.getLongitude() + "');";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
				throw new Exception(ex.getMessage());
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	    	
	    }
	    
	    public void insertRowVehicle(Vehicle vehicle) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "INSERT INTO " + VEHICLE + 
			    		     " (Name,Type,Route)" +
			                 " VALUES ('" + vehicle.getName() + "'," +
			    		     " '" + vehicle.getType() + "'," + 
			                 " '" + vehicle.getRoute() + "');";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	    	
	    }
	    
	    public void insertRowStreetName(String name) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "INSERT INTO " + STREETNAME + 
			    		     " (Name)" +
			                 " VALUES ('" + name + "');";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	    		    	
	    }
	    
		public void insertRowStreetSegment(String xa,String ya,String xb,String yb) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "INSERT INTO " + STREETSEGMENT + 
			    		     " (XA,YA,XB,YB)" +
			                 " VALUES ('" + xa + "'," +
					         " '" + ya + "'," +
					         " '" + xb + "'," +
					    	 " '" + yb + "');";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	    	
		}
		
		public void insertRowStreet(int idStreetSegment,int idStreetName) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "INSERT INTO " + STREET + 
			    		     " (IdStreetSegment,IdStreetName)" +
			                 " VALUES (" + idStreetSegment + "," +
			    		     " " + idStreetName + ");";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	 
		}
		
		 public void insertRowRoad(int idFrom,int idOn,int idTo) {
			 connection = null;
			 statement = null;
				try {
					Class.forName("org.sqlite.JDBC");
				    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
			        connection.setAutoCommit(false);
		            statement = connection.createStatement();
				    String sql = "INSERT INTO " + ROAD + 
				    		     " (Fr,Onn,Too)" +
				                 " VALUES (" + idFrom + "," +
				    		     " " + idOn + "," +
				    		     " " + idTo + ");";
				    statement.executeUpdate(sql);
				    statement.close();
				    connection.commit();
				    connection.close();
				} catch(final Exception ex) {
					ex.printStackTrace();
				} finally {
					try {
						if(!statement.isClosed())
							statement.close();		
						if(!connection.isClosed())
							connection.close();
					} catch(SQLException ex) {				
					}
				}	 
		 }
		 
		 public void insertRowRouteName(String name,String type) {
		    	connection = null;
			    statement = null;
				try {
					Class.forName("org.sqlite.JDBC");
				    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
			        connection.setAutoCommit(false);
		            statement = connection.createStatement();
				    String sql = "INSERT INTO " + ROUTENAME + 
				    		     " (Name,Type)" +
				                 " VALUES ('" + name + "' , '" + type + "');";
				    statement.executeUpdate(sql);
				    statement.close();
				    connection.commit();
				    connection.close();
				} catch(final Exception ex) {
					ex.printStackTrace();
				} finally {
					try {
						if(!statement.isClosed())
							statement.close();		
						if(!connection.isClosed())
							connection.close();
					} catch(SQLException ex) {				
					}
				}	    		    	
		    }
		 
		 public void insertRowRoute(int idRouteName,int idRoad) {
			 connection = null;
			 statement = null;
				try {
					Class.forName("org.sqlite.JDBC");
				    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
			        connection.setAutoCommit(false);
		            statement = connection.createStatement();
				    String sql = "INSERT INTO " + ROUTE + 
				    		     " (IdRouteName,IdRoad)" +
				                 " VALUES (" + idRouteName + "," +
				    		     " " + idRoad + ");";
				    statement.executeUpdate(sql);
				    statement.close();
				    connection.commit();
				    connection.close();
				} catch(final Exception ex) {
					ex.printStackTrace();
				} finally {
					try {
						if(!statement.isClosed())
							statement.close();		
						if(!connection.isClosed())
							connection.close();
					} catch(SQLException ex) {				
					}
				}	
			 
		 }
		
	    /* SELECT OPERATIONS */
	    
	    public void selectStations(ArrayList<Station> list) {
	    	connection = null;
		    statement = null;
		    Station station = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT * FROM " + STATION + ";");
			    while(result.next()) {
			    	station = new Station();
			    	station.setName(result.getString("Name"));
			    	station.setLatitude(Double.valueOf(result.getString("Latitude")));
			    	station.setLongitude(Double.valueOf(result.getString("Longitude")));
			    	list.add(station);		   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectStationNodes(ArrayList<Node> nodes) {
	    	connection = null;
		    statement = null;
		    Node node = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT * FROM " + STATION + ";");
			    while(result.next()) {
			    	node = new Node();
			    	node.setName(result.getString("Name"));
			    	node.setLatitude(Double.valueOf(result.getString("Latitude")));
			        node.setLongitude(Double.valueOf(result.getString("Longitude")));
			    	nodes.add(node);		   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectStationName(ArrayList<String> stationName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Name FROM " + STATION + ";");
			    while(result.next()) {
			    	stationName.add(result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectStationNameAndId(ArrayList<String> stationName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Nr,Name FROM " + STATION + ";");
			    while(result.next()) {
			    	stationName.add(result.getInt("Nr") + " " +
			                        result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectVehicleName(ArrayList<String> vehicleName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Name FROM " + VEHICLE + ";");
			    while(result.next()) {
			    	vehicleName.add(result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectVehicleNameAndId(ArrayList<String> vehicleName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Nr,Name FROM " + VEHICLE + ";");
			    while(result.next()) {
			    	vehicleName.add(result.getInt("Nr") + " " +
			                        result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectStreetName(ArrayList<String> streetName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Name FROM " + STREETNAME + ";");
			    while(result.next()) {
			    	streetName.add(result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public int selectIdStreetName(String name) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id FROM " + STREETNAME + 
			    		                                  " WHERE Name = '" + name + "';");
			    while(result.next()) {
			    	id = result.getInt("Id");	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public int selectIdStation(String name,String lat,String lon) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Nr FROM " + STATION + 
			    		                                  " WHERE Name = '" + name + "'" +
			    		                                  " AND Latitude = '" + lat + "'" +
			    		                                  " AND Longitude = '" + lon + "' ;");
			    if(result.next()) {
			    	id = result.getInt("Nr");	   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public void selectIdSegment(ArrayList<String> idSegmentList) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id FROM " + STREETSEGMENT + ";");
			    while(result.next()) {
			    	idSegmentList.add(result.getString("Id"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectSegments(TreeMap<String,Segment> segmentList) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id,XA,YA,XB,YB FROM " + STREETSEGMENT + ";");
			    while(result.next()) {
			    	Segment seg = new Segment(Double.valueOf(result.getString("XA")),
							    			  Double.valueOf(result.getString("YA")),
							    			  Double.valueOf(result.getString("XB")),
							    			  Double.valueOf(result.getString("YB")));
			    	segmentList.put(String.valueOf(result.getInt("Id")),seg);
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectVehicles(ArrayList<Vehicle> list) {
	    	connection = null;
		    statement = null;
		    Vehicle vehicle = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT * FROM " + VEHICLE + ";");
			    while(result.next()) {
			    	vehicle = new Vehicle();
			    	vehicle.setName(result.getString("Name"));
			    	vehicle.setType(result.getString("Type"));
			    	vehicle.setRoute(result.getString("Route"));
			    	list.add(vehicle);		    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public Street selectStreet(int idStreetName) {
	    	Street street = null;
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT sn.Name,ss.XA,ss.YA,ss.XB,ss.YB " +
			    		                                  " FROM " + STREET + " s " +
			    		                                  " INNER JOIN " + STREETSEGMENT + " ss " + " ON " +
			    		                                  " s.IdStreetSegment = ss.Id " + 
			    		                                  " INNER JOIN " + STREETNAME + " sn "+ " ON " +
			    		                                  " s.IdStreetName = sn.Id " +
			    		                                  " WHERE s.IdStreetName = " + idStreetName + ";");
			    street = new Street();
			    while(result.next()) {
			    	street.setName((result.getString("Name")));
			    	street.addSegment(new Segment(Double.valueOf(result.getString("XA")),
										    	  Double.valueOf(result.getString("YA")),
										    	  Double.valueOf(result.getString("XB")),
										    	  Double.valueOf(result.getString("YB"))));
														  
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    	return street;
	    }
	    
	    public void selectStreets(StreetTreeMap listStreet) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT sn.Name,ss.XA,ss.YA,ss.XB,ss.YB " +
			    		                                  " FROM " + STREET + " s " +
			    		                                  " INNER JOIN " + STREETSEGMENT + " ss " + " ON " +
			    		                                  " s.IdStreetSegment = ss.Id " + 
			    		                                  " INNER JOIN " + STREETNAME + " sn "+ " ON " +
			    		                                  " s.IdStreetName = sn.Id;");
			    while(result.next()) {
			    	listStreet.add(result.getString("Name"),
					    		   new Segment(Double.valueOf(result.getString("XA")),
									     	   Double.valueOf(result.getString("YA")),
									    	   Double.valueOf(result.getString("XB")),
									    	   Double.valueOf(result.getString("YB"))));
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public int selectIdStreetSegment(String xa,String xb,String ya,String yb) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id FROM " + STREETSEGMENT + 
			    		                                  " WHERE XA = '" + xa + "'" +
			    		                                  " AND YA = '" + ya + "';");//+
			    		                                 // " AND XB = '" + xb + "'" +
			    		                                 // " AND YB = '" + yb + "';");
			    while(result.next()) {
			    	id = result.getInt("Id");	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public int selectIdComleteStreet(Integer idSegment) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT IdStreetName FROM " + STREET + 
			    		                                  " WHERE IdStreetSegment = " + idSegment + ";");
			    if(result.next()) {
			    	id = result.getInt("IdStreetName");	   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public int selectIdRoad(int idFrom,int idOn,int idTo) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id FROM " + ROAD + 
			    		                                  " WHERE Fr = " + idFrom + 
			    		                                  " AND Onn = " + idOn +
			    		                                  " AND Too = " + idTo + ";");
			    if(result.next()) {
			    	id = result.getInt("Id");	   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public void selectRoads(ArrayList<FromWithOnTo> listVertex) {	
	    	StreetTreeMap streetTreeMap = new StreetTreeMap();
	    	selectStreets(streetTreeMap);
	    	connection = null;
		    statement = null;
		    FromWithOnTo vertex = null;
		    Node stationFrom = null;
		    Vehicle vehicleWith = null;
		    Street streetOn = null;
		    Node stationTo = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT sf.Name AS sfName,sf.Latitude AS sfLatitude," +
		                                                  " sf.Longitude AS sfLongitude," +
			    		                                  " sn.Name as snName,st.Name AS stName,st.Latitude AS stLatitude," +
		                                                  " st.Longitude AS stLongitude " +
			    		                                  " FROM " + ROAD + " r " +
			    		                                  " INNER JOIN " + STATION + " sf " + " ON " +
			    		                                  " r.Fr = sf.Nr " + 
			    		                                  " INNER JOIN " + STREETNAME + " sn "+ " ON " +
			    		                                  " r.Onn = sn.Id " +
			    		                                  " INNER JOIN " + STATION + " st "+ " ON " +
			    		                                  " r.Too = st.Nr ;");
			    while(result.next()) {
			    	vertex = new FromWithOnTo();	
			    	
			    	stationFrom = new Node(result.getString("sfName"),
			    			               Double.valueOf(result.getString("sfLatitude")),
			    			               Double.valueOf(result.getString("sfLongitude")));
			    	vertex.setFrom(stationFrom);
			    	
			    	vehicleWith = new Vehicle();
		
			    	streetOn = streetTreeMap.getStreet(result.getString("snName"));	  
			    	
			    	vertex.setWithAndOn(new Vertex(vehicleWith,streetOn));			    	
			    	
			    	stationTo = new Node(result.getString("stName"),
				 			             Double.valueOf(result.getString("stLatitude")),
				 			             Double.valueOf(result.getString("stLongitude")));
			    	vertex.setTo(stationTo);
			    	
			    	listVertex.add(vertex);
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public FromWithOnTo selectRoad(int id) {
	    	StreetTreeMap streetTreeMap = new StreetTreeMap();
	    	selectStreets(streetTreeMap);
	    	connection = null;
		    statement = null;
		    FromWithOnTo vertex = null;
		    Node stationFrom = null;
		    Vehicle vehicleWith = null;
		    Street streetOn = null;
		    Node stationTo = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT sf.Name AS sfName,sf.Latitude AS sfLatitude," +
		                                                  " sf.Longitude AS sfLongitude," +
			    		                                  " sn.Name as snName,st.Name AS stName,st.Latitude AS stLatitude," +
		                                                  " st.Longitude AS stLongitude " +
			    		                                  " FROM " + ROAD + " r " +
			    		                                  " INNER JOIN " + STATION + " sf " + " ON " +
			    		                                  " r.Fr = sf.Nr " + 
			    		                                  " INNER JOIN " + STREETNAME + " sn "+ " ON " +
			    		                                  " r.Onn = sn.Id " +
			    		                                  " INNER JOIN " + STATION + " st "+ " ON " +
			    		                                  " r.Too = st.Nr WHERE r.Id = " + id + ";");
			    while(result.next()) {
			    	vertex = new FromWithOnTo();	
			    	
			    	stationFrom = new Node(result.getString("sfName"),
			    			               Double.valueOf(result.getString("sfLatitude")),
			    			               Double.valueOf(result.getString("sfLongitude")));
			    	vertex.setFrom(stationFrom);
			    	
			    	vehicleWith = new Vehicle();
		
			    	streetOn = streetTreeMap.getStreet(result.getString("snName"));	  
			    	
			    	vertex.setWithAndOn(new Vertex(vehicleWith,streetOn));			    	
			    	
			    	stationTo = new Node(result.getString("stName"),
				 			             Double.valueOf(result.getString("stLatitude")),
				 			             Double.valueOf(result.getString("stLongitude")));
			    	vertex.setTo(stationTo);
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return vertex;
	    }
	    
	    public void selectRouteName(ArrayList<String> routeName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Name FROM " + ROUTENAME + ";");
			    while(result.next()) {
			    	routeName.add(result.getString("Name"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public void selectRouteNameAndType(TreeMap<String,String> listRouteName) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Name,Type FROM " + ROUTENAME + ";");
			    while(result.next()) {
			    	listRouteName.put(result.getString("Name"),
			    			          result.getString("Type"));	    	
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    }
	    
	    public int selectIdRouteName(String name) {
	    	connection = null;
		    statement = null;
		    int id = 0;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT Id FROM " + ROUTENAME + 
			    		                                  " WHERE Name = '" + name + "' ;");
			    if(result.next()) {
			    	id = result.getInt("Id");	   
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
			return id;
	    }
	    
	    public void selectRoute(int idRouteName,ArrayList<Integer> listIdRoad) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		   
		        connection.setAutoCommit(false);
		        statement = connection.createStatement();
			    ResultSet result = statement.executeQuery("SELECT IdRoad FROM " + ROUTE +
			    		                                  " WHERE IdRouteName = " + idRouteName + ";");
			    while(result.next()) {
			    	listIdRoad.add(result.getInt("IdRoad"));
			    }
			    result.close();
			    statement.close();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();		
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}
	    	
	    }
	    
	    /* DELETE OPERATIONS */
	    
	    public void deleteStation(String name) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + STATION + 
			    		     " WHERE Name = '" + name + "';";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	    	
	    }
	    
	    public void deleteVehicle(String name) {
	    	connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + VEHICLE + 
			    		     " WHERE Name = '" + name + "';";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
	    }
		public void deleteStreetName(String name) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + STREETNAME + 
			    		     " WHERE Name = '" + name + "';";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}
		public void deleteStreetSegment(int id) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + STREETSEGMENT + 
			    		     " WHERE Id = " + id + ";";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}
		public void deleteStreet(int idStreetName) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + STREET + 
			    		     " WHERE IdStreetName = " + idStreetName + ";";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}
		
		public void deleteRoad(int idRoad) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + ROAD + 
			    		     " WHERE Id = " + idRoad + ";";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}
		
		public void deleteRouteName(String name) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + ROUTENAME + 
			    		     " WHERE Name = '" + name + "';";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}
		
		public void deleteRoute(int idRoute) {
			connection = null;
		    statement = null;
			try {
				Class.forName("org.sqlite.JDBC");
			    connection = DriverManager.getConnection("jdbc:sqlite:" + DATABASE);		    
		        connection.setAutoCommit(false);
	            statement = connection.createStatement();
			    String sql = "DELETE FROM " + ROUTE + 
			    		     " WHERE Id = " + idRoute + ";";
			    statement.executeUpdate(sql);
			    statement.close();
			    connection.commit();
			    connection.close();
			} catch(final Exception ex) {
				ex.printStackTrace();
			} finally {
				try {
					if(!statement.isClosed())
						statement.close();		
					if(!connection.isClosed())
						connection.close();
				} catch(SQLException ex) {				
				}
			}	
		}

}
